package cn.chinaflame.pagodas.web;
import static cn.chinaflame.pagodas.constant.Sysconst.EXCELTITLETW;
import static cn.chinaflame.pagodas.constant.Sysconst.EXCELTITLE;
import static cn.chinaflame.pagodas.utils.LightAlgo.LightUtil.SEAT;

import java.io.File;
import java.io.FileInputStream;
import java.io.InputStream;
import java.text.DecimalFormat;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import java.util.Set;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.sql.DataSource;

import cn.chinaflame.pagodas.utils.stl.StringUtil;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.hellojavaer.poi.excel.utils.ExcelUtils;
import org.hellojavaer.poi.excel.utils.read.ExcelReadFieldMapping;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.ui.ModelMap;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;
import org.springframework.web.bind.annotation.RequestParam;
import org.springframework.web.bind.annotation.ResponseBody;
import org.springframework.web.bind.annotation.RestController;
import org.springframework.web.multipart.MultipartFile;

import com.google.common.collect.Lists;
import com.google.common.collect.Maps;

import cn.chinaflame.pagodas.annotation.ApiDoc;
import cn.chinaflame.pagodas.configuration.RedisCacheConfig;
import cn.chinaflame.pagodas.constant.Sysconst;
import cn.chinaflame.pagodas.dto.PaProductAreaDto;
import cn.chinaflame.pagodas.entity.PaProductArea;
import cn.chinaflame.pagodas.entity.PaProductPoint;
import cn.chinaflame.pagodas.entity.PaProductPosition;
import cn.chinaflame.pagodas.service.PaProductAreaService;
import cn.chinaflame.pagodas.service.PaProductPointService;
import cn.chinaflame.pagodas.service.PaProductPositionService;
import cn.chinaflame.pagodas.utils.DateTimes;
import cn.chinaflame.pagodas.utils.HttpCode;
import cn.chinaflame.pagodas.utils.IDGen;
import cn.chinaflame.pagodas.utils.InstanceUtil;
//import io.swagger.annotations.ApiOperation;
import cn.chinaflame.pagodas.utils.UploadUtil;
import cn.chinaflame.pagodas.utils.LightAlgo.LightUtil;
import cn.chinaflame.pagodas.utils.excel.DefaultExcelReadSheetProcessor;
import cn.chinaflame.pagodas.utils.excel.ExcelParamDto;
import cn.chinaflame.pagodas.utils.excel.ExcelReaderUtil;
import cn.chinaflame.pagodas.utils.excel.ExcelWebUtil;
/**
 * <p>
 * ${table.comment}  前端控制器
 * </p>
 *
 * @author pengcheng.zhao
 * @since 2018-06-21
 */
@RestController
@Transactional(rollbackFor = Exception.class)
@RequestMapping(value = "/openapi/v1/pagodas/excel/")
public class ExcelUtilsAPI extends BaseAPI{
	   Logger logger = LoggerFactory.getLogger(ExcelUtilsAPI.class);
	private static final String AREA="区";
	private static final String SEAT="01";//座号
	private static final String PILLAR="墙";
	//总行数
    private int totalRows = 0;  
    //总条数
    private int totalCells = 0; 

    //错误信息接收器
    private static String errorMsg;
    //获取总行数
    public int getTotalRows()  { return totalRows;} 
    //获取总列数
    public int getTotalCells() {  return totalCells;} 
    //获取错误信息
    public String getErrorInfo() { return errorMsg; }  
	@Autowired
	private PaProductAreaService paProductAreaService ;
	@Autowired
	private PaProductPositionService paProductPositionService;
	@Autowired
	private PaProductPointService paProductPointService;
	public String getService() {
		return null;
	}
	
	/**
	 * 把Excel灯号数据导入商品区域表，商品位置表，灯号表
	 * @param file
	 * @param request
	 * @param response
	 * @return
	 */
	@RequestMapping(value="upload",method = RequestMethod.POST)
	@ResponseBody
	public String  upload(@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response){
		String result = "";//userService.readExcelFile(file);
		String fileName = file.getOriginalFilename();//获取文件名
		List<ExcelParamDto> exceldtoList = Lists.newArrayList();
		List<String> lights = Lists.newArrayList();
//		String area = fileName.substring(0, fileName.indexOf("."));
//		if(!area.contains(AREA)) {
//			Integer.parseInt(area.replace(AREA,""));
//			return null;
//		}
		try {
			if (!ExcelReaderUtil.validateExcel(fileName)) {// 验证文件名是否合格
				return null;
			}
//			PaProductAreaDto paproductarea = paProductAreaService.add(area);
//			String areaId = paproductarea.getId();
			boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
			if (ExcelReaderUtil.isExcel2007(fileName)) {
				isExcel2003 = false;
			}
			InputStream is = file.getInputStream();
			Workbook wb = null;
			if (isExcel2003) {// 当excel是2003时,创建excel2003
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时,创建excel2007
				wb = new XSSFWorkbook(is);
			}
			// 得到第一个shell
			Sheet sheet = wb.getSheetAt(0);
			// 得到Excel的行数
			this.totalRows = sheet.getPhysicalNumberOfRows();
			// 得到Excel的列数(前提是有行数)
			if (totalRows > 1 && sheet.getRow(0) != null) {
				this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			Row title = sheet.getRow(0);
			String deviceTypeTitle = title.getCell(0).getStringCellValue();
			String areaTitle = title.getCell(1).getStringCellValue();
			String seatTitle = title.getCell(2).getStringCellValue();
			String pillarTitle = title.getCell(3).getStringCellValue();
			String layerTitle = title.getCell(4).getStringCellValue();
			String bitsTitle = title.getCell(5).getStringCellValue();
			String sumTitle = title.getCell(6).getStringCellValue();
			String deviceSortTitle = title.getCell(7).getStringCellValue();
			if(!(EXCELTITLETW.get("0").trim().equals(deviceTypeTitle)    //判断列头是否完全匹配
					&& EXCELTITLETW.get("1").trim().equals(areaTitle)
					&& EXCELTITLETW.get("2").trim().equals(seatTitle) 
					&& EXCELTITLETW.get("3").trim().equals(pillarTitle) 
					&& EXCELTITLETW.get("4").trim().equals(layerTitle) 
					&& EXCELTITLETW.get("5").trim().equals(bitsTitle) 
					&& EXCELTITLETW.get("6").trim().equals(sumTitle) 
					&& EXCELTITLETW.get("7").trim().equals(deviceSortTitle))) {
				return null;
			}
			for (int r = 1; r < totalRows; r++) {// 循环Excel行数
				Row row = sheet.getRow(r);
				if (row == null){
					continue;
				}
				ExcelParamDto exceldto = new ExcelParamDto();
				DecimalFormat df = new DecimalFormat();
//				PaProductPosition paproductposition = new PaProductPosition();
				// 循环Excel的列
				for (int c = 0; c < this.totalCells; c++) {
					Cell cell = row.getCell(c);
					if (null != cell) {
						switch(c) {
						case 0:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){//设备类型
								String deviceType =  cell.getStringCellValue();
								exceldto.setDeviceType(deviceType);
							} 
							break;
						case 1:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//区域号
								String areaVal = String.valueOf(cell.getNumericCellValue());
								areaVal = areaVal.substring(0, areaVal.length()-2);
								areaVal = areaVal.length()<2?"0"+areaVal:areaVal;
								exceldto.setArea(areaVal);
							}
							break;
						case 2:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//座号
								String seatVal =  String.valueOf(cell.getNumericCellValue());
								seatVal = seatVal.substring(0, seatVal.length()-2);
								seatVal = seatVal.length()<2?"0"+seatVal:seatVal;
								exceldto.setSeat(seatVal);
							}
							break;
						case 3:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//柱号
								String pillarVal = String.valueOf(cell.getNumericCellValue());
								pillarVal = pillarVal.substring(0, pillarVal.length()-2);
								pillarVal = pillarVal.length()<2?"0"+pillarVal:pillarVal;
								exceldto.setPillar(pillarVal);
							}
							break;
						case 4:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//行号
								String layerVal = String.valueOf(cell.getNumericCellValue());
								layerVal = layerVal.substring(0, layerVal.length()-2);
								layerVal = layerVal.length()<2?"0"+layerVal:layerVal;
								exceldto.setLayer(layerVal);
							}
							break;
						case 5:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//列号
								String bitsVal = String.valueOf(cell.getNumericCellValue());
								bitsVal = bitsVal.substring(0, bitsVal.length()-2);
								bitsVal = bitsVal.length()<2?"0"+bitsVal:bitsVal;
								exceldto.setBits(bitsVal);
							}
							break;
						case 6:
							//if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
								//String sumVal =  cell.getStringCellValue();//String.valueOf(cell.getNumericCellValue());
							//	sumVal = sumVal.substring(0, sumVal.length()-2);
								//sumVal = sumVal.length()<2?"0"+sumVal:sumVal;
							//判断是否为空和是否全部为整数
							if(StringUtils.isNotEmpty(exceldto.getBits()) && StringUtils.isNotEmpty(exceldto.getLayer())
									&& StringUtil.isNum(exceldto.getBits()) && StringUtil.isNum(exceldto.getLayer())) {
								exceldto.setSumlights(Integer.parseInt(exceldto.getBits())*Integer.parseInt(exceldto.getLayer())+"");//每墙总灯号，根据行号和列号生成
							}
							//}
							break;
						case 7:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){//设备分类，墙灯，柱灯
								String deviceSort =  cell.getStringCellValue();
								exceldto.setDeviceSort(deviceSort);
							}
							break;
						case 8:break;
						default:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//读取要排除的每个灯号
								String light =  df.format(cell.getNumericCellValue());
								logger.info("最后读取的灯号为："+light);
								light = light.replace(",", "");
								if(StringUtils.isNotEmpty(light)) {
									if(light.length() == 10)
										lights.add(light);
									if(light.length() == 9)
										lights.add("0"+light); 
								}
							}
							break;
						}
					}
				}
				exceldtoList.add(exceldto);

			}
			paProductPointService.importExcel(exceldtoList,lights) ;//导入Excel Service
			return null;
		} catch (Exception e) {
			e.printStackTrace();
			logger.error(e.getMessage());
		}
		
//		map.put(, list);
	        //解析excel，获取上传的事件单
			//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
			//和你具体业务有关,这里不做具体的示范
//			if(useList != null && !useList.isEmpty()){
//				result = "上传成功";
//			}else{
//				result = "上传失败";
//			}
			return result;

	}
	/**
	 * 更改未安装位置的灯号
	 * @param file
	 * @param request
	 * @param response
	 * @return
	 */
	
	@RequestMapping(value="excludeLights",method = RequestMethod.POST)
	@ResponseBody
	public String excludeLights(@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response) {
		String result = ""; 
		String fileName = file.getOriginalFilename();//获取文件名
		List<String> lights = Lists.newArrayList();
//		String area = fileName.substring(0, fileName.indexOf("."));
//		if(!area.contains(AREA)) {
//			Integer.parseInt(area.replace(AREA,""));
//			return null;
//		}
		try {
			if (!ExcelReaderUtil.validateExcel(fileName)) {// 验证文件名是否合格
				return null;
			}
			boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
			if (ExcelReaderUtil.isExcel2007(fileName)) {
				isExcel2003 = false;
			}
			InputStream is = file.getInputStream();
			Workbook wb = null;
			if (isExcel2003) {// 当excel是2003时,创建excel2003
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时,创建excel2007
				wb = new XSSFWorkbook(is);
			}
			// 得到第一个shell
			Sheet sheet = wb.getSheetAt(0);
			// 得到Excel的行数
			this.totalRows = sheet.getPhysicalNumberOfRows();
			// 得到Excel的列数(前提是有行数)
			if (totalRows > 0) {
				this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			for (int r = 1; r < totalRows; r++) {// 循环Excel行数
				Row row = sheet.getRow(r);
				if (row == null){
					continue;
				}
//				PaProductPosition paproductposition = new PaProductPosition();
				// 循环Excel的列
				DecimalFormat df = new DecimalFormat();  
				for (int c = 0; c < this.totalCells; c++) {
					Cell cell = row.getCell(c);
					if (null != cell) {
						if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){//若灯号前面不够10位且没0，则添加0在前面
							String light =  df.format(cell.getNumericCellValue());
							light = light.replace(",", "");
							if(StringUtils.isNotEmpty(light)) {
								lights.add(light.length()==9?"0"+light:light); 
							}
						}
					}
				}

			}
			paProductPointService.exculdeLights(lights); //排除灯号Service
		} catch (Exception e) {
			e.printStackTrace();
		}
		return "success";
	}
	
	@RequestMapping(value="upload1",method = RequestMethod.POST)
	@ResponseBody
	public String  upload1(@RequestParam(value="file",required = false)MultipartFile file,HttpServletRequest request, HttpServletResponse response){
		String result = "";//userService.readExcelFile(file);
		String fileName = file.getOriginalFilename();//获取文件名
		ExcelReaderUtil readExcel=new ExcelReaderUtil();
		Map<String,List<PaProductPosition>> map = Maps.newHashMap();
		List<PaProductPosition> list = null;
		List<ExcelParamDto> exceldtoList = Lists.newArrayList();
//		String area = fileName.substring(0, fileName.indexOf("."));
//		if(!area.contains(AREA)) {
//			Integer.parseInt(area.replace(AREA,""));
//			return null;
//		}
		  JdbcTemplate jdbcTemplate = null;//new JdbcTemplate(dataSource);
		try {
			if (!ExcelReaderUtil.validateExcel(fileName)) {// 验证文件名是否合格
				return null;
			}
//			PaProductAreaDto paproductarea = paProductAreaService.add(area);
//			String areaId = paproductarea.getId();
			boolean isExcel2003 = true;// 根据文件名判断文件是2003版本还是2007版本
			if (ExcelReaderUtil.isExcel2007(fileName)) {
				isExcel2003 = false;
			}
			InputStream is = file.getInputStream();
			Workbook wb = null;
			if (isExcel2003) {// 当excel是2003时,创建excel2003
				wb = new HSSFWorkbook(is);
			} else {// 当excel是2007时,创建excel2007
				wb = new XSSFWorkbook(is);
			}
			// 得到第一个shell
			Sheet sheet = wb.getSheetAt(0);
			// 得到Excel的行数
			this.totalRows = sheet.getPhysicalNumberOfRows();
			// 得到Excel的列数(前提是有行数)
			if (totalRows > 1 && sheet.getRow(0) != null) {
				this.totalCells = sheet.getRow(0).getPhysicalNumberOfCells();
			}
			Row title = sheet.getRow(0);
			String deviceTypeTitle = title.getCell(0).getStringCellValue();
			String areaTitle = title.getCell(1).getStringCellValue();
			String seatTitle = title.getCell(2).getStringCellValue();
			String pillarTitle = title.getCell(3).getStringCellValue();
			String layerTitle = title.getCell(4).getStringCellValue();
			String bitsTitle = title.getCell(6).getStringCellValue();
			String deviceSortTitle = title.getCell(8).getStringCellValue();
//			Set<String> cols = EXCELTITLE.keySet();
//			if(!(EXCELTITLE.get("0").trim().equals(deviceTypeTitle)    //判断列头是否相等
//					&& EXCELTITLE.get("1").trim().equals(areaTitle)
//					&& EXCELTITLE.get("2").trim().equals(seatTitle) 
//					&& EXCELTITLE.get("3").trim().equals(pillarTitle) 
//					&& EXCELTITLE.get("4").trim().equals(layerTitle) 
//					&& EXCELTITLE.get("5").trim().equals(bitsTitle) 
//					&& EXCELTITLE.get("6").trim().equals(deviceSortTitle))) {
//				return null;
//			}
			if(!(EXCELTITLE.get("0").trim().equals(deviceTypeTitle)    //判断列头是否相等
					&& EXCELTITLE.get("1").trim().equals(areaTitle)
					&& EXCELTITLE.get("2").trim().equals(seatTitle) 
					&& EXCELTITLE.get("3").trim().equals(pillarTitle) 
					&& EXCELTITLE.get("4").trim().equals(layerTitle) 
					&& EXCELTITLE.get("6").trim().equals(bitsTitle) 
					&& EXCELTITLE.get("8").trim().equals(deviceSortTitle))) {
				return null;
			}
			for (int r = 1; r < totalRows; r++) {// 循环Excel行数
				Row row = sheet.getRow(r);
				if (row == null){
					continue;
				}
				ExcelParamDto exceldto = new ExcelParamDto();
//				PaProductPosition paproductposition = new PaProductPosition();
				// 循环Excel的列
				for (int c = 0; c < this.totalCells; c++) {
					Cell cell = row.getCell(c);
					if (null != cell) {
						switch(c) {
						case 0:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
								String deviceType =  cell.getStringCellValue();
								exceldto.setDeviceType(deviceType);
							} 
							break;
						case 1:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
								String areaVal = String.valueOf(cell.getNumericCellValue());
								areaVal = areaVal.substring(0, areaVal.length()-2);
								areaVal = areaVal.length()<2?"0"+areaVal:areaVal;
								exceldto.setArea(areaVal);
							}
							break;
						case 2:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
								String seatVal =  String.valueOf(cell.getNumericCellValue());
								seatVal = seatVal.substring(0, seatVal.length()-2);
								seatVal = seatVal.length()<2?"0"+seatVal:seatVal;
								exceldto.setSeat(seatVal);
							}
							break;
						case 3:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
								String pillarVal = String.valueOf(cell.getNumericCellValue());
								pillarVal = pillarVal.substring(0, pillarVal.length()-2);
								pillarVal = pillarVal.length()<2?"0"+pillarVal:pillarVal;
								exceldto.setPillar(pillarVal);
							}
							break;
						case 4:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
								String layerVal = String.valueOf(cell.getNumericCellValue());
								layerVal = layerVal.substring(0, layerVal.length()-2);
								layerVal = layerVal.length()<2?"0"+layerVal:layerVal;
								exceldto.setLayer(layerVal);
							}
							break;
						case 6:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC){
								String bitsVal = String.valueOf(cell.getNumericCellValue());
								bitsVal = bitsVal.substring(0, bitsVal.length()-2);
								bitsVal = bitsVal.length()<2?"0"+bitsVal:bitsVal;
								exceldto.setBits(bitsVal);
							}
							break;
						case 8:
							if(cell.getCellType() == HSSFCell.CELL_TYPE_STRING){
								String deviceSort =  cell.getStringCellValue();
								exceldto.setDeviceSort(deviceSort);
							}
							break;
						default:break;	
						}
					}
				}
				String sql = "insert into excelparamdto value(?,?,?,?,?,?,?,?)";
				jdbcTemplate.update(sql, new Object[] {IDGen.randomLong(),exceldto.getDeviceType(),exceldto.getArea(),exceldto.getSeat(),exceldto.getPillar(),exceldto.getLayer(),exceldto.getBits(),exceldto.getDeviceSort()});
				//exceldtoList.add(exceldto);
		        String areasql = "select id from pa_product_area where name='"+exceldto.getArea()+AREA+"'";
		        System.out.println(areasql);
		        String areaIdStr = jdbcTemplate.queryForObject(areasql, String.class);
		       // String areaUpdateSql = "update pa_product_area set state=0,updatetime=now() where name='"+exceldto.getArea()+AREA+"'";
		        //jdbcTemplate.update(areaUpdateSql);
		        if(StringUtils.isEmpty(areaIdStr)) {
		        	String areaIdSql =  "select nextid('yw_product_area') from DUAL";
		        	areaIdStr = jdbcTemplate.queryForObject(areaIdSql, String.class);
		        	String insql = "insert into pa_product_area values(?,?,?,'',0,now(),now())";
		        	jdbcTemplate.update(insql,new Object[] {areaIdStr,exceldto.getArea()+AREA,exceldto.getArea()+SEAT} );
		        }
		        String posquerySql = "select id from pa_product_position where name=? and position_no=? and area_id=?";
		        String posUptsql = "update pa_product_position set state=0,updatetime=now() where name=? and position_no=? and area_id=?";
		        String positionId="";
				try {
					positionId = jdbcTemplate.queryForObject(posquerySql, new Object[] {exceldto.getPillar()+PILLAR,exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar(),areaIdStr},String.class);
				} catch (Exception e) {
					logger.error(e.getMessage());
				}
		        jdbcTemplate.update(posUptsql, new Object[] {exceldto.getPillar()+PILLAR,exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar(),areaIdStr});
		        if(StringUtils.isEmpty(positionId)) {
		        	String sqlStr = "select nextid('pa_product_position') from DUAL";
		        	String idStr =jdbcTemplate.queryForObject(sqlStr, String.class);
		        	String inssql="insert into pa_product_position(id,name,position_no,area_id,state,createtime,updatetime,device_type,device_sort,sumlights) values('"+idStr+"','"+exceldto.getPillar()+PILLAR+"','"+(exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar())+"','"+areaIdStr+"','0',now(),now(),'"+exceldto.getDeviceType()+"','"+exceldto.getDeviceSort()+"','"+(exceldto.getSumlights() ==null?0:exceldto.getSumlights())+"')";
		        	jdbcTemplate.execute(inssql);
		        	logger.info(inssql);
		        	positionId = idStr;
		        }
		        int rows = Integer.parseInt(exceldto.getLayer());
		        int cols = Integer.parseInt(exceldto.getBits());
		        int count = 0;
		        System.out.println("rows="+rows+"&cols="+cols);
		        for(int i=1;i<=rows;i++) {
		        	for(int j = 1;j<=cols;j++) {
		        		String lono = exceldto.getArea()+exceldto.getSeat()+exceldto.getPillar()+(i<10?"0"+i:i)+(j<10?"0"+j:j);
			        	String pointQuerySql = "select id from pa_product_point where position_id=? and area_id=? and row=? and col=? and location_no=?";
			        	String pointUptSql = "update pa_product_point set status=1,updatetime=now() where position_id=? and area_id=? and row=? and col=? and location_no=?";
			        	String point="";
						try {
							point = jdbcTemplate.queryForObject(pointQuerySql,new Object[] {positionId,areaIdStr,(i<10?"0"+i:i),(j<10?"0"+j:j),lono}, String.class);
						} catch (Exception e) {
							logger.error(e.getMessage());
						}
			        	jdbcTemplate.update(pointUptSql,new Object[] {positionId,areaIdStr,(i<10?"0"+i:i),(j<10?"0"+j:j),lono});
			        	System.out.println("StringUtils.isEmpty(point)="+StringUtils.isEmpty(point)+"###"+(i<10?"0"+i:i)+"##"+(j<10?"0"+j:j)+"###"+lono);
			        	if(StringUtils.isEmpty(point)) {
			        		String pointsqlStr = "select nextid('pa_product_point') from DUAL";
			        		String pointidStr =jdbcTemplate.queryForObject(pointsqlStr,String.class);
			        		String inpossql="insert into pa_product_point(id,position_id,area_id,row,col,location_no,vip_flg,num,status,createtime,updatetime)values"
			        				+"('"+pointidStr+"','"+positionId+"',"+areaIdStr+",'"+(i<10?"0"+i:i)+"','"+(j<10?"0"+j:j)+"','"+lono+"',0,0,1,now(),now())";
			        		logger.info(inpossql);
			        		jdbcTemplate.execute(inpossql);
			        	}
			        	count++;
		        	}
		        }
		        System.out.println("count="+count);
			}
			return null;
		} catch (Exception e) {
			e.printStackTrace();
		}
		
//		map.put(, list);
	        //解析excel，获取上传的事件单
			//至此已经将excel中的数据转换到list里面了,接下来就可以操作list,可以进行保存到数据库,或者其他操作,
			//和你具体业务有关,这里不做具体的示范
//			if(useList != null && !useList.isEmpty()){
//				result = "上传成功";
//			}else{
//				result = "上传失败";
//			}
			return result;

	}

	 
	// 上传文件(支持批量)
	@SuppressWarnings({ "rawtypes", "unchecked" })
	@RequestMapping("/info/monitoringTarget")
	@ApiDoc()//"导入excel文件"
    public Object importMonitoringTarget(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws Exception {
		List<String> fileNames = UploadUtil.uploadImage(request, false);
		String filePath="";
        if (fileNames.size() > 0) {
        	DefaultExcelReadSheetProcessor<PaProductPosition> sheetProcessor = new DefaultExcelReadSheetProcessor<PaProductPosition>();
            int sheetIndex = 0;
            List<HashMap> headMapList = ExcelWebUtil.getWriteHeadMapList(filePath, 10, sheetIndex);
	        if(headMapList.size()<=0)	throw new Exception("Can not get header information!");
	        Map<String, String> headerMap = headMapList.get(0);
            ExcelReadFieldMapping fieldMapping = ExcelWebUtil.getReadDataFieldMapping(headerMap, new PaProductPosition());
	        Map<String, String> modelHeaderMap =  null;//ExcelWebUtil.getHeadMapList(excelPath, 10, dataSheetName);
	        Set<Map.Entry<String,String>> set = headerMap.entrySet();
	        Iterator<Map.Entry<String, String>> it = set.iterator();
	        int equalHeaderCount = 0;
	        while(it.hasNext()){
	        	Map.Entry<String, String> entry =  it.next();
	        	if(modelHeaderMap.containsKey(entry.getKey()) && modelHeaderMap.get(entry.getKey()) != null && entry.getValue() != null && modelHeaderMap.get(entry.getKey()).toUpperCase().equals(entry.getValue().toUpperCase())){
	        		equalHeaderCount++;
	        	}
	        }
	        if(equalHeaderCount != set.size()){
	        	System.err.println("The uploaded file header do not match the original model file header !");
	        	modelMap.put("errMsg",  "The uploaded file header do not match the original model file header !");
	        	modelMap.put("errCode", "inValidFormat");
	        	return setErrorModelMap(modelMap);
	        }
            sheetProcessor.setSheetIndex(sheetIndex);// required
            sheetProcessor.setStartRowIndex(1);//
            sheetProcessor.setTargetClass(PaProductPosition.class);// required
            sheetProcessor.setFieldMapping(fieldMapping);// required
            sheetProcessor.setTrimSpace(true);
            InputStream in = new FileInputStream(new File(filePath));
            ExcelUtils.read(in, sheetProcessor);
            List<PaProductPosition> dataList = sheetProcessor.getDataList();
            //List<TiMonitoringTarget> emptyList = sheetProcessor.getDataList();
            List<PaProductPosition> insertList = Lists.newArrayList();
//            Parameter parameter = new Parameter("tiMonitoringTargetService", "importMonitoringTarget").setList(insertList);
//	        provider.execute(parameter);
            
            modelMap.put("fileNames", fileNames);
            if(dataList.size() > insertList.size()){
            	modelMap.put("inValidUpload","上传文件中有"+ (dataList.size() - insertList.size())+"条Process_Owner为空的非法数据未上传成功！");
            }
            return setSuccessModelMap(modelMap);
        } else {
            setModelMap(modelMap, HttpCode.BAD_REQUEST);
            modelMap.put("msg", "请选择要上传的文件！");
            return modelMap;
        }
    }
	
		
		// 上传文件(支持批量)
		@SuppressWarnings({ "rawtypes", "unchecked" })
		@RequestMapping("/info/rsa")
//	    @ApiOperation(value = "导入excel文件")
	    public Object uploadRSA(HttpServletRequest request, HttpServletResponse response, ModelMap modelMap) throws Exception {
			String fileName = "information_rsa_cn.xlsx";//information_rsa.xlsx";
			String excelPath = "/download/claim/"+fileName;
//			String filePath ="";
			List<String> fileNames = UploadUtil.uploadImage(request, false);
	        if (fileNames.size() > 0) {
	        	 String filePath = UploadUtil.getUploadDir(request);
	        	 filePath =  filePath+fileNames.get(0);
	            int sheetIndex = 0;
	            List<HashMap> headMapList = ExcelWebUtil.getWriteHeadMapList(filePath, 35, sheetIndex, 0);
		        if(headMapList.size()<=0)	throw new Exception("Can not get header information!");
		        Map<String, String> headerMap = headMapList.get(0);
		       // Map<String,String> convertHeaderMap = Maps.newHashMap();
		        
	           // ExcelReadFieldMapping fieldMapping = ExcelWebUtil.getReadDataFieldMapping1(headerMap, Constants.TiOriginRasVO);//new TiOriginRas()
		        ExcelReadFieldMapping fieldMapping = new ExcelReadFieldMapping();
		        int columnIndex = 0;
		        for (Map.Entry<String, String> entry :  Sysconst.EXCELTITLE.entrySet()) {
		        	//String header = entry.getValue()==null?"":entry.getValue().toString();
		        	fieldMapping.put(ExcelUtils.convertColIntIndexToCharIndex(columnIndex), entry.getKey());
		        	columnIndex++;
		        }
	            String dataSheetName = "Data";
		        Map<String, String> modelHeaderMap = ExcelWebUtil.getHeadMapList(excelPath, 35, dataSheetName);
		        Set<Map.Entry<String,String>> set = headerMap.entrySet();
		        Iterator<Map.Entry<String, String>> it = set.iterator();
		        int equalHeaderCount = 0;
		        while(it.hasNext()){
		        	Map.Entry<String, String> entry =  it.next();
		        	if(modelHeaderMap.containsKey(entry.getKey()) && modelHeaderMap.get(entry.getKey()) != null && entry.getValue() !=null && modelHeaderMap.get(entry.getKey()).toUpperCase().equals(entry.getValue().toUpperCase())){
		        		equalHeaderCount++;
		        	}
		        }
		        if(equalHeaderCount != set.size()){
		        	//setModelMap(modelMap, HttpCode.BAD_REQUEST);
		        	//StringBuffer rest = new StringBuffer( "The uploaded file header do not match the original model file header !");
		        	/*JSONObject rest = new JSONObject();
		        		rest.put("msg",  "The uploaded file header do not match the original model file header !");
		        		rest.put("errCode", "inValidFormat");*/
		        	// modelMap.put("msg", "The uploaded file header do not match the original model file header !");
		        	//throw new Exception("The uploaded file header do not match the original model file header !");
		        	System.err.println("The uploaded file header do not match the original model file header !");
		        	modelMap.put("errMsg",  "The uploaded file header do not match the original model file header !");
		        	modelMap.put("errCode", "inValidFormat");
		        	return setErrorModelMap(modelMap);
		        }
		        List<Map<String,Object>> dataList = new ArrayList<Map<String,Object>>() ;
		        Map<String, Object> map = new HashMap<String, Object>();
		        DefaultExcelReadSheetProcessor<Map<String,Object>> sheetProcessor = new DefaultExcelReadSheetProcessor<Map<String,Object>>();
	            sheetProcessor.setSheetIndex(sheetIndex);// required
	            sheetProcessor.setStartRowIndex(1);//
	            sheetProcessor.setTargetClass((Class<Map<String, Object>>) map.getClass());// required
	            sheetProcessor.setFieldMapping(fieldMapping);// required
	            sheetProcessor.setTrimSpace(true);
	            
	            
	            InputStream in = new FileInputStream(new File(filePath));
	            ExcelUtils.read(in, sheetProcessor);
	            dataList = sheetProcessor.getDataList();
	            List<PaProductPosition> insertList = Lists.newArrayList();
	            for(Map<String,Object> tmt : dataList){ //StringUtils.isNotEmpty(tmt.get("caseOpenDateTime")) && DateUtil.validYMDate(tmt.get("caseOpenDateTime")) && StringUtils.isNotEmpty(tmt.get("issueId")) && StringUtils.isNotEmpty(tmt.get("vin"))
	            	//boolean condition1 = tmt.get("caseOpenDateTime") !=null && StringUtils.isNotEmpty(String.valueOf(tmt.get("caseOpenDateTime"))) && tmt.get("caseOpenDateTime") != null && DateUtil.validYMDate(String.valueOf(tmt.get("caseOpenDateTime"))) && tmt.get("issueId") != null && StringUtils.isNotEmpty(String.valueOf(tmt.get("issueId"))) && tmt.get("vin") != null && StringUtils.isNotEmpty(String.valueOf(tmt.get("vin")));
	            	boolean cd1 = tmt.get("开案时间") !=null && StringUtils.isNotEmpty(String.valueOf(tmt.get("开案时间"))) && DateTimes.validYMDate(String.valueOf(tmt.get("开案时间"))) ;
	            	boolean cd2 =tmt.get("任务编号") != null && StringUtils.isNotEmpty(String.valueOf(tmt.get("任务编号"))) && tmt.get("VIN码") != null && StringUtils.isNotEmpty(String.valueOf(tmt.get("VIN码")));
	            	boolean cd3 = tmt.get("开案时间") instanceof java.util.Date;
	            	if((cd1||cd3) && cd2){
	            		Map<String,Object> mapbean = Maps.newHashMap();
	            		//Map<String,Object> tmpbean = Maps.newHashMap();
	            		Set<Map.Entry<String,Object>> entrys = tmt.entrySet();
	            		Iterator<Map.Entry<String,Object>> entryit = entrys.iterator();
	             		while(entryit.hasNext()){
	            			Map.Entry<String,Object> entry = entryit.next();
	            		    //tmpbean.put(String.valueOf(Constants.TiOriginRasVO.get(entry.getKey())), entry.getValue());
//	            			mapbean.put(String.valueOf(Constants.TiOriginRasMappingVO.get(String.valueOf(Constants.TiOriginRasVO.get(entry.getKey())))), entry.getValue() instanceof java.util.Date?DateUtil.format(entry.getValue()):entry.getValue());
	            		}
	             		PaProductPosition rsa = new PaProductPosition();
	            		InstanceUtil.transMap2Bean(mapbean, rsa) ;
	            		insertList.add(rsa);
	            	} 
	            }
//	            Parameter delParameter = new Parameter("tiOriginRasService", "deleteAll");
//	            provider.execute(delParameter);
	            //DecimalFormat df = new DecimalFormat("0.00") ;  
	            // long start = System.currentTimeMillis();
	            // long freeMem = Runtime.getRuntime().freeMemory();  
//	            Parameter parameter = new Parameter("tiOriginRasService", "importRSA").setList(insertList);
//		        provider.execute(parameter);
		       // System.out.println("##########################时间："+(System.currentTimeMillis()-start)/1000+"内存："+(df.format((Runtime.getRuntime().freeMemory()-freeMem)/1000000F) + " MB"));
	            if(dataList.size() > insertList.size()){
	            	modelMap.put("inValidUpload","上传文件中有"+ (dataList.size() - insertList.size())+"条非2017-01-01格式的开案时间或任务编号为空或vin号为空的非法数据未上传成功！");
	            }
	            modelMap.put("fileNames", fileNames);
	            return setSuccessModelMap(modelMap);
	        } else {
	            setModelMap(modelMap, HttpCode.BAD_REQUEST);
	            modelMap.put("msg", "请选择要上传的文件！");
	            return modelMap;
	        }
	    }

}